# _*_coding:utf-8_*_
import sqlite3
import traceback
import pickle as cPickle
import logging
import time
import sys

if sys.version_info > (3,):
    buffer = memoryview
else:
    import buffer as buffer

from . import DmConstant
import datetime
from utils.log_util import loggings as atsLogger

USE_TDATA_STR = True
DEBUG = logging.debug


class TimeData(object):
    def __init__(self, data):
        """
        data:
        None  no time setting
        a Tuple  specify a time
        str "Now"   use the current time
        """
        self.data = data


def adapt_TimeData(timeData):
    if timeData.data is None:
        return ""
    try:
        return time.strftime(DmConstant.TIMSTAMP_FORMAT, timeData.data)
    except:
        print("Error while adpting timedata: %s" % traceback.format_exc())
        return ""


def convert_TimeData(s):
    if not s:
        return None
    try:
        return time.strptime(s, DmConstant.TIMSTAMP_FORMAT)
    except:
        print("Error while converting timedata: %s" % traceback.format_exc())
        return None


class DateTimeData(object):
    def __init__(self, data):
        """
        data:
        None  no time setting
        a Tuple  specify a time
        str "Now"   use the current time
        """
        self.data = data


def adapt_DateTimeData(dateTimeData):
    if dateTimeData.data is None:
        return ""
    try:
        return datetime.datetime.strftime(dateTimeData.data, DmConstant.TIMSTAMP_FORMAT_M)
    except:
        print("Error while adpting timedata: %s" % traceback.format_exc())
        return ""


def convert_DateTimeData(s):
    if not s:
        return None
    try:
        return datetime.datetime.strptime(s, DmConstant.TIMSTAMP_FORMAT_M)
    except:
        print("Error while converting timedata: %s" % traceback.format_exc())
        return None


class SeqData(object):
    def __init__(self, data):
        self.data = data


def adapt_SeqData(seqData):
    if USE_TDATA_STR:
        return str(seqData.data)
    return buffer(cPickle.dumps(seqData.data, 2))


def convert_SeqData(s):
    if USE_TDATA_STR:
        #        print '-----',s
        if s == '<null>' or s == '':
            return None
        return eval(s)
    return cPickle.loads(s)


class FileStreamData(object):
    def __init__(self, file_name):
        self.file_name = file_name


def adapt_fileStreamData(fsData):
    path = fsData.file_name
    sys.stdin = open(path, 'rb')
    obj = sys.stdin.read()
    return buffer(obj)


def get_file_path():
    return "D:/kkk.a"


def convert_fileStreamData(s):
    export_path = get_file_path()
    # sys.stdout = file(export_path, "wb")
    sys.stdout.write(s)
    return export_path


sqlite3.register_adapter(SeqData, adapt_SeqData)
sqlite3.register_adapter(FileStreamData, adapt_fileStreamData)
sqlite3.register_adapter(TimeData, adapt_TimeData)
sqlite3.register_adapter(DateTimeData, adapt_DateTimeData)

sqlite3.register_converter("SDATA", convert_SeqData)
sqlite3.register_converter("FSDATA", convert_fileStreamData)
sqlite3.register_converter("TIMEDATA", convert_TimeData)
sqlite3.register_converter("DATETIMEDATA", convert_DateTimeData)


class DbBase:

    def __init__(self, db_file, i_level=None):
        self.file = db_file
        self.con = None
        self.cur = None
        try:
            self.con = sqlite3.connect(db_file, isolation_level=i_level,
                                       detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
            self.cur = self.con.cursor()
        except:
            print("Error occured while connecting db file %s :" % db_file)
            print(traceback.format_exc())
            return

    def create_table(self, table_name, drop=False, *constrains, **columns):
        DEBUG('create_table')
        columns_def = "pkid INTEGER PRIMARY KEY,"
        for n, v in columns.items():
            columns_def += "%s %s," % (n, v)
        for v in constrains:
            columns_def += "%s," % v
        columns_def = columns_def[:len(columns_def) - 1]
        sql = "DROP TABLE IF EXISTS %s" % table_name
        self.cur.execute(sql)
        sql = "CREATE TABLE %s (%s)" % (table_name, columns_def)
        self.cur.execute(sql)
        self.con.commit()

    def is_table_exist(self, table_name):
        DEBUG('Query whether table %s exists' % table_name)
        sql = "select 1 from sqlite_master where type='table' and name=?"
        self.cur.execute(sql, (table_name,))
        result = self.cur.fetchall()
        if not result:
            return False
        return len(result) > 0

    def __parse_condition(self, condition):
        if isinstance(condition, dict):
            tmp_str = ""
            v_tup = ()
            for key in condition.keys():
                con_value = condition.get(key)
                if isinstance(con_value, list):
                    tmp_str = tmp_str + "%s in (%s) and " % (key, "?," * (len(con_value) - 1) + "?")
                    for i_vlaue in con_value:
                        v_tup = v_tup + (i_vlaue,)
                else:
                    tmp_str = tmp_str + "%s=? and " % key
                    v_tup = v_tup + (condition[key],)

            if tmp_str:
                tmp_str = tmp_str.rstrip(" and ")
            return tmp_str, v_tup

        if isinstance(condition, list):
            list_str = ""
            list_tup = ()
            for i, ele in enumerate(condition):
                t_str, t_v_tup = self.__parse_condition(ele)
                if i != 0:
                    list_str = list_str + " and "
                list_str = list_str + t_str
                list_tup = list_tup + t_v_tup
            return list_str, list_tup
        if isinstance(condition, tuple):
            tup_str = ""
            tup_tup = ()
            for i, ele in enumerate(condition):
                tu_str, tu_v_tup = self.__parse_condition(ele)
                if i != 0:
                    tup_str = tup_str + " or "
                tup_str = tup_str + tu_str
                tup_tup = tup_tup + tu_v_tup
            return tup_str, tup_tup
        return None, None

    def is_row_exist_pkid(self, table_name, conditions):
        """
            conditions:
            could be composite of dict, list and tuple.
            list means all the elements in it are "and" relationship.
            [condition1, condition2]  ==>  condition1 and condition2
            tuple means all the elements in it are "or" relationship.
            (condition1, condition2)  ==>  condition1 or condition2
            dict means the item-value pair of the conditions.
            {"NAME":"ITM", "NUM":[1,2,3]}==> "where NAME=ITM and NUM in (1,2,3)"
        """
        DEBUG('Query whether row exists for condition %s' % str(conditions))
        tmp_str, v_tup = self.__parse_condition(conditions)
        sql = "select pkid from %s where %s" % (table_name, tmp_str)
        #        print 'LLAAA : ',sql
        self.cur.execute(sql, v_tup)
        result = self.cur.fetchall()
        if not result:
            return (False, None)
        return (True, result[0][0])

    def is_row_exist(self, table_name, conditions):
        """
            conditions:
            could be composite of dict, list and tuple.
            list means all the elements in it are "and" relationship.
            [condition1, condition2]  ==>  condition1 and condition2
            tuple means all the elements in it are "or" relationship.
            (condition1, condition2)  ==>  condition1 or condition2
            dict means the item-value pair of the conditions.
            {"NAME":"ITM", "NUM":[1,2,3]}==> "where NAME=ITM and NUM in (1,2,3)"
        """
        DEBUG('Query whether row exists for condition %s' % str(conditions))
        tmp_str, v_tup = self.__parse_condition(conditions)
        sql = "select 1 from %s where %s" % (table_name, tmp_str)
        self.cur.execute(sql, v_tup)
        result = self.cur.fetchall()
        if not result:
            return False
        return len(result) > 0

    #        tmp_str = ""
    #        v_tup = ()
    #        for key in conditions.keys():
    #            tmp_str=tmp_str+"%s=? and "%key
    #            v_tup=v_tup+(conditions[key],)
    #        if tmp_str:
    #            tmp_str = tmp_str.rstrip(" and ")
    #        sql = "select 1 from %s where %s" % (table_name,tmp_str)
    #        self.cur.execute(sql,v_tup)
    #        result = self.cur.fetchall()
    #        if not result:
    #            return False
    #        return len(result)>0

    def exec_sql(self, sql):
        self.cur.execute(sql)

    def query(self, sql, size=-1, tup=None):
        """
        size: -1  to get all the matched rows
              Int to get matched rows with value of size parameter

        """
        DEBUG("sql=%s" % sql)
        #        self.con.row_factory = sqlite.Row
        if tup is None:
            self.cur.execute(sql)
        else:
            print("sql", sql)
            print("tup", tup)
            self.cur.execute(sql, tup)

        if size == -1:
            results = self.cur.fetchall()
            print("query result", results)
            return results
        elif size == 1:
            result = self.cur.fetchone()
            return result
        else:
            return self.cur.fetchmany(size)

    #            t1=time.time()
    #            icons=cur.execute(sql,tup)
    #            t2=time.time()
    #            print " execute insert takes", t2-t1
    #            return icons

    def insert(self):
        pass

    def insert_many(self, table_name, columns, values, commit_flag=True):
        """
            columns: list of column name
            values: list of tuple of column values
        """
        column_str = ",".join(columns)
        sql = "insert into %s (%s) values (%s)" % (table_name, column_str, "?," * (len(columns) - 1) + "?")
        self.con.executemany(sql, values)
        self.con.commit()

    def insert_row(self, table_name, commit_flag=False, **kargs):
        """
            kargs:
            {"ITEM1":1,"ITEM2":2}
        """
        columns = values = ''
        param_t = []
        for n, v in kargs.items():
            columns += "%s," % n
            values += "?,"
            param_t.append(v)
        #            if isinstance(v,str) or isinstance(v,list):
        #                values+="'%s',"%str(v)
        #            elif isinstance(v,float) or isinstance(v,int):
        #                values+="%g,"%v
        #            elif isinstance(v,datetime.datetime):
        #                values+="'%s',"%v.isoformat()
        #            else:
        #                values+="'%s',"%str(v)

        columns = columns[:len(columns) - 1]
        values = values[:len(values) - 1]
        print("table_name", table_name)
        print("columns", columns)
        print("values", values)

        sql = "INSERT INTO %s (%s) VALUES(%s)" % (table_name, columns, values)
        self.cur.execute(sql, tuple(param_t))
        if commit_flag:
            self.con.commit()
        return self.cur.lastrowid

    def get_latest_id(self, table_name):
        sql = "select max(rowid) as mid from %s" % table_name
        result = self.query(sql, 1)
        if not result:
            return -1
        else:
            return result[0]

    def update_tabl(self, table_name, conditions, updates):
        if len(updates) == 0:
            return
        sql = "update %s set "
        val_tup = ()
        str_tup = (table_name,)
        for upd in updates.keys():
            sql = sql + "%s=?,"
            str_tup = str_tup + (upd,)
            val_tup = val_tup + (updates[upd],)
        sql = sql.rstrip(',')
        if len(conditions) > 0:
            sql = sql + " Where "
            for con in conditions.keys():
                sql = sql + "%s=? and "
                str_tup = str_tup + (con,)
                val_tup = val_tup + (conditions[con],)
            sql = sql.rstrip(' and ')
        sql = sql % str_tup
        try:
            self.cur.execute(sql, val_tup)
            self.commit()
        except:
            atsLogger.error("Error in updating table:\n%s" % traceback.format_exc())

    # 組裝sql語句
    def delete(self, table_name, conditions):
        sql = "delete from %s"
        val_tup = ()
        str_tup = (table_name,)
        if conditions and len(conditions) > 0:
            sql = sql + " where "
            for con in conditions.keys():
                sql = sql + "%s=? and "
                val_tup = val_tup + (conditions[con],)
                str_tup = str_tup + (con,)
            sql = sql.rstrip(" and ")
        sql = sql % str_tup
        try:
            self.cur.execute(sql, val_tup)
            self.commit()
        except:
            print("Error in deleting from table %s:\n%s" % (table_name, traceback.format_exc()))

    def commit(self):
        self.con.commit()

    def close(self):
        if self.cur:
            self.cur.close()


if __name__ == '__main__':

    import sys

    i = 7
    k = DbBase("D:/aaas.db")

    if i == 0:
        table_name = "PROJECT_FILE"
        values = {}
        values["PROJECT_RUN_ID"] = 1
        values["FILE_TYPE"] = 'WDF'
        values["FILE_CONTENT"] = FileStreamData('D:/aaa.jpg')
        k.insert_row(table_name, commit_flag=True, **values)
    elif i == 1:
        col_def = {}
        col_def["PROJECT_RUN_ID"] = "integer"
        col_def["FILE_TYPE"] = "TEXT"
        col_def["FILE_CONTENT"] = "FSData"
        table_name = "PROJECT_FILE"
        k.create_table(table_name, **col_def)
        k.close()
    elif i == 5:
        table_name = 'sqlite_master'
        # pa = ()
        pa1 = {'name': ['stocks', 'mm']}
        pa2 = {'name': 'll44l'}
        pa = (pa1, pa2)
        k.is_row_exist(table_name, pa)
    elif i == 6:
        conditions = {"pkid": 1, 'NAME': 'NBA'}
        updates = {'MODEL_TYPES': "['b','kkk','xy']", 'NAME': 'RRTTT'}
        k.update_tabl("BUSINESS", conditions, updates)
    elif i == 7:
        #        conditions = {"pkid":2,'NAME':'kkk'}
        conditions = {}
        k.delete("BUSINESS", conditions)
    elif i == 2:
        try:
            sql = "select FILE_CONTENT from PROJECT_FILE where 1=?"
            b = (1,)
            result = k.query(sql, 1, b)
        except:
            print(traceback.format_exc())
        finally:
            k.close()
